-- EXEC SP_WAFER_PKG_CMOS_STANDARD_COST_REPORT 2010, 1
 
 ALTER PROC SP_WAFER_PKG_CMOS_STANDARD_COST_REPORT ( @YEAR INT, @QUARTER INT)
 AS
 BEGIN
 	DECLARE @FAB_CURSOR CURSOR 
 	DECLARE @DIEBANK_ProcessStep_ID INT
 	DECLARE @WaferCost	NUMERIC(18,4)
 	DECLARE @BGCost		NUMERIC(18,4)
 	DECLARE @GDW INT
 	DECLARE @Yield		NUMERIC(18,4)
 	DECLARE @Total_Wafer_Cost	NUMERIC(18,4)
 	DECLARE @Cumm_Cost_Per_Die	NUMERIC(18,4)
 	
 	CREATE TABLE #COST_TABLE ( [Part Number] VARCHAR(50), [GDW] INT, [Fab Cost] NUMERIC(18,4), 
 			[Back Grind Step Cost] NUMERIC(18,4), [Back Grind CUMM Cost] NUMERIC (18, 4), 
 			[Total Wafer Cost] NUMERIC (18, 4), [Die Bank Cost] NUMERIC (18, 4), ProcessStep_Id INT
 		 )
 
 	INSERT INTO #COST_TABLE( [Part Number], ProcessStep_Id ) 
 	SELECT P.Part_Number, P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID AND PS.PS_Type = 'Die Bank' AND
 	P.ProcessStep_ID NOT IN ( SELECT ProcessStep_ID FROM Process_Parameter WHERE Parameter_key = 'IS_MEMS_WAFER' AND Parameter_Value = 'TRUE'  )
 
 	SET @FAB_CURSOR = CURSOR FOR SELECT ProcessStep_Id FROM #COST_TABLE
 	OPEN @FAB_CURSOR
 	FETCH NEXT FROM @FAB_CURSOR INTO @DIEBANK_ProcessStep_ID
 	WHILE @@FETCH_STATUS = 0
 	BEGIN
 		SELECT @WaferCost = [Wafer Cost], @BGCost = [BackGrind Cost]
 		  FROM [Wafer_Pkg_Cost_Table] WHERE ProcessStep_ID = @DIEBANK_ProcessStep_ID AND Quarter = @Quarter AND Year = @Year
		
		SELECT @GDW = Parameter_Value FROM Process_Parameter WHERE Parameter_key = 'FIN_GDW' AND ProcessStep_ID = ( SELECT TOP 1 ProcessStep_ID FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @DIEBANK_ProcessStep_ID, 0 ) WHERE PS_TYPE = 'FAB' )
		IF( @GDW IS NULL OR @GDW < 1  )
		BEGIN
			SELECT @GDW = Parameter_Value FROM Process_Parameter WHERE Parameter_key = 'Gross Die/Wafer' AND ProcessStep_ID = ( SELECT TOP 1 ProcessStep_ID FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @DIEBANK_ProcessStep_ID, 0 ) WHERE PS_TYPE = 'FAB' )
		END
 
 		UPDATE #COST_TABLE SET [Fab Cost] = @WaferCost, [Back Grind Step Cost] = ISNULL( @BGCost, 0.0),[GDW] = @GDW,
 		[Back Grind CUMM Cost] = @WaferCost + ISNULL( @BGCost, 0.0), [Total Wafer Cost] = @WaferCost + ISNULL( @BGCost, 0.0), 
 		[Die Bank Cost] = (@WaferCost + ISNULL( @BGCost, 0.0))/ISNULL( @GDW, 1 )
 		WHERE ProcessStep_Id = @DIEBANK_ProcessStep_ID

		SET @WaferCost	= 0.0
		SET @BGCost		= 0.0
		SET @GDW		= 0
 
 		FETCH NEXT FROM @FAB_CURSOR INTO @DIEBANK_ProcessStep_ID
 	END 
 	CLOSE @FAB_CURSOR
 	DEALLOCATE @FAB_CURSOR
 
 	SELECT [Part Number], [GDW], [Fab Cost], 
 			[Back Grind Step Cost], [Back Grind CUMM Cost], 
 			[Total Wafer Cost], [Die Bank Cost] FROM #COST_TABLE ORDER BY ProcessStep_Id
 
 END

